IF OBJECT_ID('ssf.job_run_until_complete') IS NOT NULL
	DROP PROCEDURE ssf.job_run_until_complete
GO

CREATE PROCEDURE ssf.job_run_until_complete
	@job_name NVARCHAR(128)
	, @poll_interval TIME(1) = NULL
	, @run_status INT = NULL OUTPUT
AS
/*
<object_header>

<object_summary>
	<purpose><![CDATA[

	]]></purpose>
</object_summary>

</object_header>
*/

SET NOCOUNT ON

-- ============
/*<code_comment><![CDATA[declare dynamic sql variables]]></code_comment>*/
DECLARE	@proc_error INT
			, @sql_error INT
			, @error_message NVARCHAR(2048)
			, @error_line INT
			, @row_count BIGINT
			, @sql VARCHAR(8000)
			, @nsql NVARCHAR(4000)
			, @crlf CHAR(2) = CHAR(13) + CHAR(10)
			, @quote CHAR(1) = CHAR(39)
			, @tab CHAR(1) = CHAR(9)
-- ============

-- ============
/*<code_comment><![CDATA[declare local variables]]></code_comment>*/
DECLARE	@run_after_datetime DATETIME = NULL
-- ============

-- ============
/*<code_comment><![CDATA[initialize output parameters]]></code_comment>*/
SET @run_status = NULL
-- ============

-- ============
/*<code_comment><![CDATA[check parameters]]></code_comment>*/
IF NULLIF(@job_name, N'') IS NULL
BEGIN
	RAISERROR('A required parameter is blank or null.', 16, 1)
	RETURN 1
END
-- ============

-- ============
/*<code_comment><![CDATA[start job]]></code_comment>*/
SET @run_after_datetime = GETDATE()

BEGIN TRY
	EXECUTE @sql_error = msdb.dbo.sp_start_job @job_name = @job_name
END TRY
BEGIN CATCH
	SELECT @sql_error = ERROR_NUMBER()
		, @error_message = ERROR_MESSAGE()
END CATCH

IF @sql_error <> 0
BEGIN
	IF NULLIF(@error_message, '') IS NULL
		SET @error_message = 'Failed to start the job <' + @job_name + '>.'
	
	RAISERROR(@error_message, 16, 1)
	RETURN @sql_error
END
-- ============

-- ============
/*<code_comment><![CDATA[wait for job to complete]]></code_comment>*/
BEGIN TRY
	EXECUTE @sql_error = ssf.job_wait_until_complete
		@job_name = @job_name
		, @run_after_datetime = @run_after_datetime
		, @poll_interval = @poll_interval
		, @run_status = @run_status OUTPUT
END TRY
BEGIN CATCH
	SELECT @sql_error = ERROR_NUMBER()
		, @error_message = ERROR_MESSAGE()
END CATCH

IF @sql_error <> 0
BEGIN
	IF NULLIF(@error_message, '') IS NULL
		SET @error_message = 'Failed waiting for the job <' + @job_name + '> to complete.'
	
	RAISERROR(@error_message, 16, 1)
	RETURN @sql_error
END
-- ============

-- ============
/*<code_comment><![CDATA[handle job status]]></code_comment>*/
IF @run_status = 0
BEGIN
	RAISERROR('The job <%s> failed.', 16, 1, @job_name)
	RETURN 1
END
-- ============

RETURN @@ERROR
GO
